

package com.yungu.swift.admin.utils;

import com.yungu.swift.utils.DateUtils;
import com.yungu.swift.utils.StringUtils;
import org.apache.poi.ss.usermodel.*;

import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @author Zerrion
 * @version 1.0
 * @description Excel 文档操作工具类
 * @date 2017/10/11 10:34
 */
public class WorkbookUtil {

    /**
     * 获取对应列的最大字符串长度
     *
     * @param rowCount
     * @param cellNum
     * @param sheet
     * @return
     */
    public static int getMaxColumnWidth(int rowCount, int cellNum, Sheet sheet) {
        int width = 0;
        for (int i = 0; i < rowCount; i++) {
            String value = getCellValue(sheet.getRow(i).getCell(cellNum));
            width = Math.max(width, (value.length() + 2) * 256);
        }
        return width;
    }

    /**
     * 自动调整表格的宽度
     *
     * @param sheet
     */
    public static void autoColumnWidth(Sheet sheet) {
        int rowCount = sheet.getLastRowNum();
        //如果没有行数 则不进行处理
        if (rowCount == 0) {
            return;
        }
        Row row = sheet.getRow(1);
        //获取列数
        short cellCount = row.getLastCellNum();
        for (int i = 0; i < cellCount; i++) {
            // 设置当前列自定义宽度
            sheet.setColumnWidth(i, getMaxColumnWidth(rowCount, i, sheet));
        }
    }

    /**
     * 获取单元格字符串值
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        return getCellValue(cell, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"));
    }

    /**
     * 获取单元格字符串值
     *
     * @param cell
     * @param sdf
     * @return
     */
    public static String getCellValue(Cell cell, SimpleDateFormat sdf) {
        // //取不到单元格直接返回空
        if (cell == null) {
            return "";
        }
        int cellType = cell.getCellType();
        String cellValue = "";
        switch (cellType) {
            //日期或数字
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 日期
                    cellValue = sdf.format(cell.getDateCellValue());
                } else {
                    // 数字
                    cellValue = new DecimalFormat("#").format(cell.getNumericCellValue());
                }
                break;
            //文本
            case Cell.CELL_TYPE_STRING:
                // 空白
            case Cell.CELL_TYPE_BLANK:
                cellValue = cell.getStringCellValue();
                break;
            //公式
            case Cell.CELL_TYPE_FORMULA:
                break;
            // 布尔型
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            // 错误
            case Cell.CELL_TYPE_ERROR:
                break;
            default:
                cellValue = "";
        }
        return cellValue.trim();
    }

    /**
     * 根据关键词查找对应的get方法
     *
     * @param objectClass
     * @param fieldName
     * @return
     */
    public static Method getGetMethod(Class objectClass, String fieldName) {
        StringBuffer sb = new StringBuffer();
        sb.append("get");
        sb.append(fieldName.substring(0, 1).toUpperCase());
        sb.append(fieldName.substring(1));
        try {
            return objectClass.getMethod(sb.toString());
        } catch (Exception e) {
        }
        return null;
    }

    /**
     * 通过反射给excel模板文件写入数据
     *
     * @param sheet    excel面板
     * @param dataList 数据源
     * @param clazz    数据源反射类
     * @param sdf      日期格式
     * @throws Exception
     */
    public static void writeDataToExcelBySheet(Sheet sheet, List dataList, Class clazz, SimpleDateFormat sdf) throws Exception {
        // 获取关键词那行
        Row row = sheet.getRow(1);
        // 获取列数
        short columnCount = row.getLastCellNum();

        for (int i = 0, len = dataList.size(); i < len; i++) {
            Row dataRow = sheet.createRow(i + 2);
            // 循环为每列赋值
            for (int j = 0; j < columnCount; j++) {
                String propertyString = row.getCell(j).getStringCellValue();
                if (StringUtils.isEmpty(propertyString)) {
                    continue;
                }
                Method getMethod = getGetMethod(clazz, propertyString);
                if (getMethod != null) {
                    Cell cell = dataRow.createCell(j);
                    // 获取单元格需要设置的值
                    Object cellValue = getMethod.invoke(dataList.get(i));
                    if (cellValue != null) {
                        CellStyle cellStyle = row.getCell(j).getCellStyle();
                        cell.setCellStyle(cellStyle);
                        if (cellValue instanceof Date) {
                            cell.setCellValue(sdf.format(cellValue));
                        } else {
                            cell.setCellValue((String) cellValue);
                        }
                    }
                } else {
                    dataRow.createCell(j).setCellValue("");
                }
            }
        }
        // 删除key那行
        if (dataList.size() == 0) {
            sheet.removeRow(row);
        } else {
            sheet.shiftRows(2, sheet.getLastRowNum(), -1);
        }

    }

    /**
     * 通过反射给excel模板文件写入数据
     *
     * @param sheet    excel面板
     * @param dataList 数据源
     * @param clazz    数据源反射类
     * @throws Exception
     */
    public static void writeDataToExcelBySheet(Sheet sheet, List dataList, Class clazz) throws Exception {
        writeDataToExcelBySheet(sheet, dataList, clazz, new SimpleDateFormat("yyyy/M/d"));
    }


    public static Boolean checkDataFormat(String value) {
        try {
            DateUtils.parse(value, "yyyy-MM-dd");
        } catch (NumberFormatException e) {
            return false;
        }
        return true;
    }
}
